﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class Customers
    {
        private string DBConnect = ConfigurationManager.ConnectionStrings["SS.Properties.Settings.SSZZConnectionString"].ToString();
        private string SP_GetCustomers = "Customers_GetCustomersByName";
        private string SP_CheckCustomers = "Customers_CheckCustomersByName";
        private string SP_SaveCustomers = "Customers_SaveCustomers";
        private string SP_DeleteCustomers = "Customers_DeleteCustomers";
        private string SP_GetCustomerNames = "Customers_GetCustomerNames";

        public DataTable GetCustomers(string CustomerName)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_GetCustomers;
            cm.Connection = cn;
            SqlParameter sqlParameter = new SqlParameter("CustomerName", CustomerName);
            sqlParameter.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public DataTable GetCustomerNames()
        {
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_GetCustomerNames;
            cm.Connection = cn;
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public bool CheckCustomers(string CustomerName)
        {
            bool IsExist = false;
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_CheckCustomers;
            cm.Connection = cn;
            SqlParameter sqlParameter = new SqlParameter("CustomerName", CustomerName);
            sqlParameter.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(dt);
            cn.Close();
            if (dt.Rows.Count > 0)
            {
                IsExist = true;
            }
            return IsExist;
        }

        public bool SaveCustomers(string CustomerName, string PhoneNumber, string CustomerDescription)
        {
            bool IsSave = true;
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_SaveCustomers;
            cm.Connection = cn;
            SqlParameter sqlParameter1 = new SqlParameter("CustomerName", CustomerName);
            sqlParameter1.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter1);
            SqlParameter sqlParameter2 = new SqlParameter("PhoneNumber", PhoneNumber);
            sqlParameter2.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter2);
            SqlParameter sqlParameter3 = new SqlParameter("CustomerDescription", CustomerDescription);
            sqlParameter3.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter3);
            try
            {
                cn.Open();
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                IsSave = false;
            }
            cn.Close();
            return IsSave;
        }

        public bool DeleteCustomers(string IDs)
        {
            bool IsDelete = true;
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_DeleteCustomers;
            cm.Connection = cn;
            SqlParameter sqlParameter1 = new SqlParameter("IDs", IDs);
            sqlParameter1.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter1);
            try
            {
                cn.Open();
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                IsDelete = false;
            }
            cn.Close();
            return IsDelete;
        }
    }
}
